
************************************************************************************************
************************************************************************************************
*This .do file creates the SAT data file used for the project

*Inputs:
*1. SAT data files from the NCERDC from 2009-2019

*Output
*1. "/data_analysis/Value Added Generate/output/sat_2009_2019.dta"
************************************************************************************************
************************************************************************************************

*First, do years 2009-2014, putting them on the 1600 SAT scale
clear all

foreach n of numlist 9 0 1 2 3 4 {
clear
if `n'==9{
use "/data/Student/SAT/mastsat09pub.dta"
}
else if `n'!=9{
use "/data/Student/SAT/mastsat1`n'pub.dta"
}

destring SATMRECN SATVRECN SATW WRITING_MC WRITING_ES ,replace
gen sat_score=(SATVRECN+SATMRECN+SATW)*10
drop if sat_score==.
drop if mastid==.

*Use this table to concord old SAT 2400(Critical Reading + Writing + Math) to New SAT 1600 (Evidence-Based Reading and Writing + Math). URL: https://collegereadiness.collegeboard.org/pdf/higher-ed-brief-sat-concordance.pdf
gen sat=.
foreach m of numlist 0(10)90{
replace sat=400+`m' if sat_score==600+`m'
}
foreach m of numlist 0(10)60{
replace sat=500+`m' if sat_score==700+`m'
}
replace sat=580 if sat_score==770
replace sat=590 if sat_score==780
replace sat=600 if sat_score==790
replace sat=610 if sat_score==800
replace sat=620 if sat_score==810
replace sat=630 if sat_score==820
replace sat=640 if sat_score==830
replace sat=650 if sat_score==840
replace sat=660 if sat_score==850
replace sat=670 if sat_score==860
replace sat=680 if sat_score==870
replace sat=690 if sat_score==880 | sat_score==890
replace sat=700 if sat_score==900
replace sat=710 if sat_score==910 | sat_score==920
replace sat=720 if sat_score==930
replace sat=730 if sat_score==940 | sat_score==950
replace sat=740 if sat_score==960 | sat_score==970
replace sat=750 if sat_score==980
replace sat=760 if sat_score==990 | sat_score==1000
replace sat=770 if sat_score==1010
replace sat=780 if sat_score==1020 | sat_score==1030
replace sat=790 if sat_score==1040
replace sat=800 if sat_score==1050 | sat_score==1060
replace sat=810 if sat_score==1070 | sat_score==1080
replace sat=820 if sat_score==1090
replace sat=830 if sat_score==1100 | sat_score==1110
replace sat=840 if sat_score==1120
replace sat=850 if sat_score==1130 | sat_score==1140
replace sat=860 if sat_score==1150
replace sat=870 if sat_score==1160 | sat_score==1170
replace sat=880 if sat_score==1180
replace sat=890 if sat_score==1190 | sat_score==1200
replace sat=900 if sat_score==1210
replace sat=910 if sat_score==1220 | sat_score==1230
replace sat=920 if sat_score==1240
replace sat=930 if sat_score==1250 | sat_score==1260
replace sat=940 if sat_score==1270
replace sat=950 if sat_score==1280 | sat_score==1290
replace sat=960 if sat_score==1300
replace sat=970 if sat_score==1310
replace sat=980 if sat_score==1320 | sat_score==1330
replace sat=990 if sat_score==1340
replace sat=1000 if sat_score==1350 | sat_score==1360
replace sat=1010 if sat_score==1370
replace sat=1020 if sat_score==1380 | sat_score==1390
replace sat=1030 if sat_score==1400 | sat_score==1410
replace sat=1040 if sat_score==1420
replace sat=1050 if sat_score==1430 | sat_score==1440
replace sat=1060 if sat_score==1450
replace sat=1070 if sat_score==1460 | sat_score==1470
replace sat=1080 if sat_score==1480
replace sat=1090 if sat_score==1490 | sat_score==1500
replace sat=1100 if sat_score==1510
replace sat=1110 if sat_score==1520 | sat_score==1530
replace sat=1120 if sat_score==1540 | sat_score==1550
replace sat=1130 if sat_score==1560
replace sat=1140 if sat_score==1570 | sat_score==1580
replace sat=1150 if sat_score==1590
replace sat=1160 if sat_score==1600 | sat_score==1610
replace sat=1170 if sat_score==1620
replace sat=1180 if sat_score==1630 | sat_score==1640
replace sat=1190 if sat_score==1650
replace sat=1200 if sat_score==1660 | sat_score==1670
replace sat=1210 if sat_score==1680 | sat_score==1690
replace sat=1220 if sat_score==1700
replace sat=1230 if sat_score==1710 | sat_score==1720
replace sat=1240 if sat_score==1730
replace sat=1250 if sat_score==1740 | sat_score==1750
replace sat=1260 if sat_score==1760
replace sat=1270 if sat_score==1770 | sat_score==1780
replace sat=1280 if sat_score==1790
replace sat=1290 if sat_score==1800 | sat_score==1810
replace sat=1300 if sat_score==1820 | sat_score==1830
replace sat=1310 if sat_score==1840
replace sat=1320 if sat_score==1850 | sat_score==1860
replace sat=1330 if sat_score==1870
replace sat=1340 if sat_score==1880 | sat_score==1890
replace sat=1350 if sat_score==1900 | sat_score==1910
replace sat=1360 if sat_score==1920
replace sat=1370 if sat_score==1930 | sat_score==1940
replace sat=1380 if sat_score==1950 | sat_score==1960
replace sat=1390 if sat_score==1970
replace sat=1400 if sat_score==1980 | sat_score==1990
replace sat=1410 if sat_score==2000 | sat_score==2010
replace sat=1420 if sat_score==2020
replace sat=1430 if sat_score==2030 | sat_score==2040
replace sat=1440 if sat_score==2050 | sat_score==2060
replace sat=1450 if sat_score==2070 | sat_score==2080
replace sat=1460 if sat_score==2090
replace sat=1470 if sat_score==2100 | sat_score==2110
replace sat=1480 if sat_score==2120 | sat_score==2130
replace sat=1490 if sat_score==2140 | sat_score==2150
replace sat=1500 if sat_score==2160 | sat_score==2170
replace sat=1510 if sat_score==2180 | sat_score==2190 | sat_score==2200
replace sat=1520 if sat_score==2210 | sat_score==2220
replace sat=1530 if sat_score==2230 | sat_score==2240
replace sat=1540 if sat_score==2250 | sat_score==2260
replace sat=1550 if sat_score==2270 | sat_score==2280 | sat_score==2290
replace sat=1560 if sat_score==2300 | sat_score==2310
replace sat=1570 if sat_score==2320 | sat_score==2330
replace sat=1580 if sat_score==2340 | sat_score==2350
replace sat=1590 if sat_score==2360 | sat_score==2370 | sat_score==2380
replace sat=1600 if sat_score==2390 | sat_score==2400
drop sat_score
ren sat sat_score

gen month=substr(LATSTSAT,1,2)
gen year=("20" + substr(LATSTSAT,3,2))

destring month year, replace
gen sat_date = mdy(month, 15, year)

duplicates tag mastid, gen(g)

egen min_date=min(sat_date), by(mastid)

drop if sat_date!=min_date & g>0

duplicates tag mastid, gen(h)
drop if h>0

keep mastid sat_score

if `n'==9{
gen year=2009
qui compress
save "/data_analysis/Value Added Generate/output/sat_2009.dta", replace
}
else if `n'!=9{
gen year=201`n'
qui compress
save "/data_analysis/Value Added Generate/output/sat_201`n'.dta", replace
}
}


*Now do 2014-2019. Note that in 2017, SAT changes back to 1600 scale.  Putting all SATs on that scale
clear all
foreach n of numlist 5 6 7 8 9 {
clear all

use "/data/Student/SAT/collegeboard1`n'pub.dta"

if `n'<=6{
destring SAT_CR_MR SAT_MATH_MR SAT_WRITE_MR,replace
gen sat_score=(SAT_CR_MR+SAT_MATH_MR+SAT_WRITE_MR)
gen sat=.
foreach m of numlist 0(10)90{
replace sat=400+`m' if sat_score==600+`m'
}
foreach m of numlist 0(10)60{
replace sat=500+`m' if sat_score==700+`m'
}
replace sat=580 if sat_score==770
replace sat=590 if sat_score==780
replace sat=600 if sat_score==790
replace sat=610 if sat_score==800
replace sat=620 if sat_score==810
replace sat=630 if sat_score==820
replace sat=640 if sat_score==830
replace sat=650 if sat_score==840
replace sat=660 if sat_score==850
replace sat=670 if sat_score==860
replace sat=680 if sat_score==870
replace sat=690 if sat_score==880 | sat_score==890
replace sat=700 if sat_score==900
replace sat=710 if sat_score==910 | sat_score==920
replace sat=720 if sat_score==930
replace sat=730 if sat_score==940 | sat_score==950
replace sat=740 if sat_score==960 | sat_score==970
replace sat=750 if sat_score==980
replace sat=760 if sat_score==990 | sat_score==1000
replace sat=770 if sat_score==1010
replace sat=780 if sat_score==1020 | sat_score==1030
replace sat=790 if sat_score==1040
replace sat=800 if sat_score==1050 | sat_score==1060
replace sat=810 if sat_score==1070 | sat_score==1080
replace sat=820 if sat_score==1090
replace sat=830 if sat_score==1100 | sat_score==1110
replace sat=840 if sat_score==1120
replace sat=850 if sat_score==1130 | sat_score==1140
replace sat=860 if sat_score==1150
replace sat=870 if sat_score==1160 | sat_score==1170
replace sat=880 if sat_score==1180
replace sat=890 if sat_score==1190 | sat_score==1200
replace sat=900 if sat_score==1210
replace sat=910 if sat_score==1220 | sat_score==1230
replace sat=920 if sat_score==1240
replace sat=930 if sat_score==1250 | sat_score==1260
replace sat=940 if sat_score==1270
replace sat=950 if sat_score==1280 | sat_score==1290
replace sat=960 if sat_score==1300
replace sat=970 if sat_score==1310
replace sat=980 if sat_score==1320 | sat_score==1330
replace sat=990 if sat_score==1340
replace sat=1000 if sat_score==1350 | sat_score==1360
replace sat=1010 if sat_score==1370
replace sat=1020 if sat_score==1380 | sat_score==1390
replace sat=1030 if sat_score==1400 | sat_score==1410
replace sat=1040 if sat_score==1420
replace sat=1050 if sat_score==1430 | sat_score==1440
replace sat=1060 if sat_score==1450
replace sat=1070 if sat_score==1460 | sat_score==1470
replace sat=1080 if sat_score==1480
replace sat=1090 if sat_score==1490 | sat_score==1500
replace sat=1100 if sat_score==1510
replace sat=1110 if sat_score==1520 | sat_score==1530
replace sat=1120 if sat_score==1540 | sat_score==1550
replace sat=1130 if sat_score==1560
replace sat=1140 if sat_score==1570 | sat_score==1580
replace sat=1150 if sat_score==1590
replace sat=1160 if sat_score==1600 | sat_score==1610
replace sat=1170 if sat_score==1620
replace sat=1180 if sat_score==1630 | sat_score==1640
replace sat=1190 if sat_score==1650
replace sat=1200 if sat_score==1660 | sat_score==1670
replace sat=1210 if sat_score==1680 | sat_score==1690
replace sat=1220 if sat_score==1700
replace sat=1230 if sat_score==1710 | sat_score==1720
replace sat=1240 if sat_score==1730
replace sat=1250 if sat_score==1740 | sat_score==1750
replace sat=1260 if sat_score==1760
replace sat=1270 if sat_score==1770 | sat_score==1780
replace sat=1280 if sat_score==1790
replace sat=1290 if sat_score==1800 | sat_score==1810
replace sat=1300 if sat_score==1820 | sat_score==1830
replace sat=1310 if sat_score==1840
replace sat=1320 if sat_score==1850 | sat_score==1860
replace sat=1330 if sat_score==1870
replace sat=1340 if sat_score==1880 | sat_score==1890
replace sat=1350 if sat_score==1900 | sat_score==1910
replace sat=1360 if sat_score==1920
replace sat=1370 if sat_score==1930 | sat_score==1940
replace sat=1380 if sat_score==1950 | sat_score==1960
replace sat=1390 if sat_score==1970
replace sat=1400 if sat_score==1980 | sat_score==1990
replace sat=1410 if sat_score==2000 | sat_score==2010
replace sat=1420 if sat_score==2020
replace sat=1430 if sat_score==2030 | sat_score==2040
replace sat=1440 if sat_score==2050 | sat_score==2060
replace sat=1450 if sat_score==2070 | sat_score==2080
replace sat=1460 if sat_score==2090
replace sat=1470 if sat_score==2100 | sat_score==2110
replace sat=1480 if sat_score==2120 | sat_score==2130
replace sat=1490 if sat_score==2140 | sat_score==2150
replace sat=1500 if sat_score==2160 | sat_score==2170
replace sat=1510 if sat_score==2180 | sat_score==2190 | sat_score==2200
replace sat=1520 if sat_score==2210 | sat_score==2220
replace sat=1530 if sat_score==2230 | sat_score==2240
replace sat=1540 if sat_score==2250 | sat_score==2260
replace sat=1550 if sat_score==2270 | sat_score==2280 | sat_score==2290
replace sat=1560 if sat_score==2300 | sat_score==2310
replace sat=1570 if sat_score==2320 | sat_score==2330
replace sat=1580 if sat_score==2340 | sat_score==2350
replace sat=1590 if sat_score==2360 | sat_score==2370 | sat_score==2380
replace sat=1600 if sat_score==2390 | sat_score==2400
drop sat_score
ren sat sat_score
}
else if `n'>=7{
gen sat_score=SAT_Total_Score_MR
}

drop if sat_score==.
drop if mastid==.

if `n'==5{
gen month=substr(SAT_TEST_DT_MR,1,2)
gen year=("20" + substr(SAT_TEST_DT_MR,5,2))
destring month year, replace
gen sat_date = mdy(month, 15, year)
}
else if `n'==6{
gen month=substr(SAT_TEST_DT_MR,6,2)
gen year=substr(SAT_TEST_DT_MR,1,4)
destring month year, replace
gen sat_date = mdy(month, 15, year)
}
else if `n'>=7{
gen sat_date=SAT_ASMT_DT_MR
}

duplicates tag mastid, gen(g)

egen min_date=min(sat_date), by(mastid)

drop if sat_date!=min_date & g>0

duplicates tag mastid, gen(h)
drop if h>0

keep mastid sat_score

gen year=201`n'
qui compress
save "/data_analysis/Value Added Generate/output/sat_201`n'.dta", replace
}

*Append all together
clear all
foreach year of numlist 2009(1)2019{
append using "/data_analysis/Value Added Generate/output/sat_`year'.dta"
erase "/data_analysis/Value Added Generate/output/sat_`year'.dta"
}

*Take first sat score
duplicates tag mastid, gen(g)
egen min_year=min(year), by(mastid)
drop if year!=min_year & g>0

drop year g min_year

compress
save "/data_analysis/Value Added Generate/output/sat_2009_2019.dta", replace









